package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class FI_DeclarationOfVAT30008 extends SvrProcess {
   
	class line{
		public String getRow() {
			return row;
		}
		public void setRow(String row) {
			this.row = row;
		}
		public String getC_bpartner_id() {
			return c_bpartner_id;
		}
		public void setC_bpartner_id(String c_bpartner_id) {
			this.c_bpartner_id = c_bpartner_id;
		}
		public String getFi_tin() {
			return fi_tin;
		}
		public void setFi_tin(String fi_tin) {
			this.fi_tin = fi_tin;
		}
		public String getFi_bin_iin() {
			return fi_bin_iin;
		}
		public void setFi_bin_iin(String fi_bin_iin) {
			this.fi_bin_iin = fi_bin_iin;
		}
		public String getDocumentno() {
			return documentno;
		}
		public void setDocumentno(String documentno) {
			this.documentno = documentno;
		}
		public String getDateinvoiced() {
			return dateinvoiced;
		}
		public void setDateinvoiced(String dateinvoiced) {
			this.dateinvoiced = dateinvoiced;
		}
		public String getDateacct() {
			return dateacct;
		}
		public void setDateacct(String dateacct) {
			this.dateacct = dateacct;
		}
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getTaxbaseamt() {
			return taxbaseamt;
		}
		public void setTaxbaseamt(String taxbaseamt) {
			this.taxbaseamt = taxbaseamt;
		}
		public String getTaxamt() {
			return taxamt;
		}
		public void setTaxamt(String taxamt) {
			this.taxamt = taxamt;
		}
		public String getTaxamt2() {
			return taxamt2;
		}
		public void setTaxamt2(String taxamt2) {
			this.taxamt2 = taxamt2;
		}
		public String getCaption() {
			return caption;
		}
		public void setCaption(String caption) {
			this.caption = caption;
		}
		public String getPeriod() {
			return period;
		}
		public void setPeriod(String period) {
			this.period = period;
		}
		String row;
		String c_bpartner_id;
		String fi_tin;
		String fi_bin_iin;
		String documentno;
		String dateinvoiced;
		String dateacct;
		String name;
		String taxbaseamt;
		String taxamt;
		String taxamt2;
		String caption;
		String period;
		
	}
	
   /** Current context    */
   private Properties m_ctx;
   /**     */
   private ProcessInfo pi;
   /** Account Sign                    */
   private String                AccountSign = "N";
   /** */
   private Timestamp startDate = null;
   private Timestamp startDate2 = null; 
   private Timestamp endDate = null; 
   private Timestamp endDate2 = null;    
   
   private Timestamp            DateAcct_From = null;
   private Timestamp            DateAcct_To = null;
   /**    Client Parameter                    */
   private int                    AD_Client_ID = 0;
   /**    Org Parameter                    */
   private int                    AD_Org_ID = 0;
   /**    Account Parameter                */
   private int                    Account_ID = 0;
   /**    BPartner Parameter                */
   private int                    C_BPartner_ID = 0;
   /** User Element 1 Parameter        */
   private int                    UserElement1_ID = 0;
   /**    Parameter Where Clause            */
   //private StringBuffer        m_parameterWhere = new StringBuffer();
   /** */
   private static CLogger    s_log    = CLogger.getCLogger (BalanceSheets.class);
   /** */
   private String path = "";

   /**
    *  Prepare - e.g., get Parameters.
    */
   @Override
   protected void prepare() {
       log.info("");
       m_ctx = Env.getCtx();
       pi = getProcessInfo();
       ProcessInfoParameter[] para = getParameter();
       for (int i = 0; i < para.length; i++)
       {
           String name = para[i].getParameterName();
           if (para[i].getParameter() == null);
           else if (name.equals("startDate"))
               startDate = (Timestamp) para[i].getParameter();
           else if (name.equals("endDate"))
               endDate = (Timestamp) para[i].getParameter();
           else if (name.equals("startDate2"))
               startDate2 = (Timestamp) para[i].getParameter();
           else if (name.equals("endDate2"))
               endDate2 = (Timestamp) para[i].getParameter();           
           else
               log.log(Level.SEVERE, "Unknown Parameter: " + name);
       }
	   AD_Client_ID = getAD_Client_ID();
	   AD_Org_ID = getAD_Org_ID();
       path = Util.localFilePath;
       /// here another code)
   }
   @Override
   protected String doIt() throws Exception {
       // Types of cells for
       Label label;
       Number number;
       // There are checks for a specified pattern
       String templateName = "FI_DeclarationOfVAT300.08_tmpl.xls";
       //
       MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
       if(entry == null)
           throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
//       
//       // We define a path to generate
//       String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
//       StringBuffer fullPath = new StringBuffer(path);
//       fullPath.append("Temporary Balance Sheets");
//       fullPath.append(fileExtension);    
//       //
       File templateCopy = new File(Util.localFilePath + "FI_DeclarationOfVAT300.08.xls");
       File temporaryFile = entry.getFile(Util.localFilePath + templateName);
       Workbook tableBook = null;
       WritableWorkbook copy = null;
       WritableSheet sheet = null;
       try {
            tableBook = Workbook.getWorkbook(temporaryFile);
            copy = Workbook.createWorkbook(templateCopy, tableBook);
            sheet = copy.getSheet(0);
       } catch (Exception e) {
           ADialog.error(999, null, "Error tableBook. " + e.getMessage());
           return "Error tableBook.";
       }
       

//
//      ExcelCell cellStart =  Utils.getCellStart(tableBook,">>");
    // Style for value column
      WritableCellFormat valuestyle = new WritableCellFormat();
      valuestyle.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
      valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
      valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
      valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
      valuestyle.setAlignment(Alignment.RIGHT);
      valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
      valuestyle.setWrap(true);
      
      List<line> l = getInvoices();
      int count = l.size();   
      int row = 7;

// РНН
   		String sRNN = getRNN(getOrgBPartner_ID(AD_Org_ID));

   		WritableFont arial10ptBoldRNN = new WritableFont(WritableFont.ARIAL, 10,	WritableFont.NO_BOLD);
   		WritableCellFormat wcfRNN = new WritableCellFormat(arial10ptBoldRNN);
   		wcfRNN.setAlignment(Alignment.CENTRE);
   		wcfRNN.setVerticalAlignment(VerticalAlignment.CENTRE);

   		label = new Label(0, 3, sRNN, wcfRNN);
   		sheet.addCell(label);
/*   		
   // Имя организации
   		String sName = getOrgName(AD_Org_ID);
   		WritableCellFormat wcfsName = new WritableCellFormat(arial10ptBoldRNN);
   		wcfsName.setAlignment(Alignment.CENTRE);
   		wcfsName.setVerticalAlignment(VerticalAlignment.CENTRE);

   		label = new Label(23, 4, sName, wcfsName);
   		sheet.addCell(label);
*/
   // БИК
   		String sBIN = getBIN(getOrgBPartner_ID(AD_Org_ID));
   		WritableCellFormat wcfBIN = new WritableCellFormat(arial10ptBoldRNN);
   		wcfBIN.setAlignment(Alignment.CENTRE);
   		wcfBIN.setVerticalAlignment(VerticalAlignment.CENTRE);

   		label = new Label(2, 3, sBIN, wcfBIN);
   		sheet.addCell(label);
      
      String caption =  "Реестр счетов-фактур \n"
		+ " по приобретенным товарам, работам, услугам в течение отчетного налогового периода \n"
		+ " c " + new java.text.SimpleDateFormat("dd.MM.yyyy").format(startDate).toString() + " по " +  new java.text.SimpleDateFormat("dd.MM.yyyy").format(endDate).toString();
      WritableCellFormat captionStyle = new WritableCellFormat();
      captionStyle.setAlignment(Alignment.CENTRE);
      captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
      
      label = new Label(0, 0, caption, captionStyle);
      sheet.addCell(label);
      
      
//      for(int j=0; j < count; j++)
//    		sheet.insertRow(12);  
    	
      
      for(int j=0; j < count; j++){

    	 line li = l.get(j);
//   	   System.out.println(li.);
        label = new Label(0, row-1, Integer.valueOf(j+1).toString(), valuestyle);
        sheet.addCell(label);
        
        label = new Label(1, row-1, "", valuestyle);
        sheet.addCell(label);
        
        label = new Label(2, row-1, li.fi_tin, valuestyle);
        sheet.addCell(label);
        
        label = new Label(3, row-1, li.fi_bin_iin, valuestyle);
        sheet.addCell(label);
        
        label = new Label(4, row-1, li.documentno, valuestyle);
        sheet.addCell(label);
        
        label = new Label(5, row-1, li.dateinvoiced, valuestyle);
        sheet.addCell(label);

        label = new Label(6, row-1, "", valuestyle);
        sheet.addCell(label);
        
        label = new Label(7, row-1, "", valuestyle);
        sheet.addCell(label);        
        
        label = new Label(8, row-1, li.taxbaseamt, valuestyle);
        sheet.addCell(label);
        
        label = new Label(9, row-1, li.taxamt, valuestyle);
        sheet.addCell(label);
        
        label = new Label(10, row-1, li.taxamt2, valuestyle);
        sheet.addCell(label);
        
        label = new Label(9, 3, li.period, valuestyle);
        sheet.addCell(label);
        
        row++;
   	   
      }      

//    ///  
      copy.write();
      copy.close();
      tableBook.close();
      temporaryFile.delete();
//      
        try{    
             String fileName = Util.localFilePath + "FI_DeclarationOfVAT300.08.xls";
             String command = "excel \""+fileName+"\"";
             Runtime.getRuntime().exec("cmd /c start "+command);
         }catch(Exception e){
             e.printStackTrace();
         }
       
      return Msg.translate(m_ctx, "Success");
   }
   
   private List<line> getInvoices(){
	   
       List<line> list = new ArrayList<line>();
       //
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       
       String sql = //"select c_invoice_id from adempiere.c_invoice where between ?  and ? ";//dateacct = ?";//'04.07.2012'";
    		   	" SELECT "
    			+ " row_number() over (order by invoice.c_invoice_id) "
    			+ " ,bpartner.c_bpartner_id "
    			+ " ,bpartner.fi_tin "
    			+ " ,bpartner.fi_bin_iin "
    			+ " ,invoice.fi_invoiceno	"
    			+ " ,to_char(invoice.dateinvoiced,'dd.mm.yyyy') dateinvoiced "
    			+ " ,to_char(invoice.dateacct,'dd.mm.yyyy') dateacct "
    			+ " ,doctype.name "
//    			//+ " -- 	,invoiceline.taxamt "
    			+ " ,to_char(invoicetax.taxbaseamt,'9 999 999 999D99') taxbaseamt "
    			+ " ,to_char(invoicetax.taxamt,'999 999 999 999D99') taxamt "
    			+ " ,to_char(invoicetax.taxamt,'999 999 999 999D99') taxamt2 "	
//    			+ " , 'Реестр счетов-фактур "
//    			+ " по приобретенным товарам, работам, услугам в течение отчетного налогового периода "
//    			+ " c ' || to_char(date( ? ),'dd.mm.yyyy') || ' по ' || to_char(date( ? ),'dd.mm.yyyy') "
//    			+ " ' caption "
    			+ " , ('квартал ' || case "
    			+ "     				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 1 then 1 "
    			+ "     				when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 2 then 1 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 3 then 1 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 4 then 2 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 5 then 2 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 6 then 2 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 7 then 3 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 8 then 3 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 9 then 3 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 10 then 4 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 11 then 4 "
    			+ " 	when cast(EXTRACT(MONTH from invoice.dateinvoiced) as int) = 12 then 4 "
    			+ " end "
    			+ " || ' год ' || cast(EXTRACT(YEAR from invoice.dateinvoiced) as int)) period "
    			+ " FROM "
    			+ " c_invoice invoice "
//    			//+ " -- 	INNER JOIN c_invoiceline invoiceline on invoiceline.c_invoice_id = invoice.c_invoice_id "
    			+ " LEFT JOIN c_invoicetax invoicetax on invoicetax.c_invoice_id = invoice.c_invoice_id "
    			+ " LEFT JOIN c_doctype doctype on doctype.c_doctype_id = invoice.c_doctype_id "
    			+ " LEFT JOIN c_bpartner bpartner on bpartner.c_bpartner_id = invoice.c_bpartner_id "
    			+ " WHERE doctype.c_doctype_id = 1000005 "
    			+ " and invoice.dateacct BETWEEN ? and ? "
    			+ " and invoice.dateinvoiced BETWEEN ? and ? "
    			+ " and invoice.docstatus = 'CO' " +
    			" order by invoice.dateacct";
				;
       //
           try
           {
               pstmt = DB.prepareStatement(sql,null);

               pstmt.setTimestamp(1, startDate);
               pstmt.setTimestamp(2, endDate);
               pstmt.setTimestamp(3, startDate2);
               pstmt.setTimestamp(4, endDate2);
               
               rs = pstmt.executeQuery();    
               while (rs.next ())
               {
            	   line l = new line();
                   l.setRow(String.valueOf(rs.getInt(1)));
                   l.setC_bpartner_id(String.valueOf(rs.getInt(2)));
                   l.setFi_tin(rs.getString(3));
                   l.setFi_bin_iin(rs.getString(4));
                   l.setDocumentno(rs.getString(5));
                   l.setDateinvoiced(rs.getString(6));
                   l.setDateacct(rs.getString(7));
                   l.setName(rs.getString(8));
                   l.setTaxbaseamt(rs.getString(9));
                   l.setTaxamt(rs.getString(10));
                   l.setTaxamt2(rs.getString(11));
                   //l.setCaption(rs.getString(11));
                   l.setPeriod(rs.getString(12));
                   
                   list.add(l);
               }
           }
           catch (SQLException e)
           {
               s_log.log(Level.SEVERE, "product", e);
           }
           finally
           {
               DB.close(rs, pstmt);
               rs = null; pstmt = null;
           }    
           return list;
   } 


	private String getRNN(int C_BPartner_ID) {
		String result = "";
		MBPartner bPartner = new MBPartner(getCtx(), C_BPartner_ID, null);
		result = bPartner.getFI_TIN();
		return result;
	}

	private String getBIN(int C_BPartner_ID) {
		String result = "";
		MBPartner bPartner = new MBPartner(getCtx(), C_BPartner_ID, null);
		result = bPartner.getFI_BIN_IIN();
		return result;
	}

	private String getOrgName(int AD_Org_ID) {
		String result = "";
		MOrg org = new MOrg(getCtx(), AD_Org_ID, null);
		result = org.getName();
		return result;
	}

	protected int getOrgBPartner_ID(int AD_Org_ID) {
		int result = -1;
		MOrg org = new MOrg(getCtx(), AD_Org_ID, null);
		result = org.getLinkedC_BPartner_ID(null);
		return result;
	}
	
	private int getAD_Org_ID() {
		int result = -1;
		String sql = "SELECT AD_Org_ID FROM AD_PInstance WHERE AD_PInstance_ID=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement(sql, get_TrxName());
			pstmt.setInt(1, getAD_PInstance_ID());
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				result = rs.getInt(1);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql, e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		return result;
	}
	

   
}